﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Activity.Helper;
using Activity.Model;
using NHibernateVertica;

namespace Activity.API
{
    public class TimeTrackingReportHandler
    {
        public VerticaDataHelper Helper { get; set; } = new VerticaDataHelper();

        public IList<TimeTrackingReportModel.ListModel> GetReport()
        {
            try
            {
                var time = DateTime.Now;
                var todayTime = $"{time:yyyy-MM-dd}";
                var yestodayTime = $"{time.AddDays(-1):yyyy-MM-dd}";
                var lastweekTime = $"{time.AddDays(-7):yyyy-MM-dd}";
                var sql = $@"SELECT  ProvinceName ,
        BillDate ,
        ShopQty ,
        FactAMT ,
        SalePartQty ,
        SaleBillPartQty ,
        FactPartAMT ,
        SaleBillQty ,
        KpiQty ,
        ( CAST(ROUND(ISNULL(( FactPartAMT / NULLIF(SaleBillPartQty, 0) ), 0),
                     2) AS NUMERIC(20, 2)) ) AS UnitPrice ,
        ( CAST(ROUND(ISNULL(( SaleBillQty * 100 / NULLIF(KpiQty, 0) ), 0), 4) AS NUMERIC(20,
                                                              4)) ) AS Closing ,
        ( CAST(ROUND(ISNULL(( SalePartQty / NULLIF(SaleBillPartQty, 0) ),
                            0), 4) AS NUMERIC(20, 2)) ) AS JointRate
FROM    ndw.ndw_timeTracking_report
WHERE   BillDate IN ( '{todayTime}', '{yestodayTime}', '{lastweekTime}' )
ORDER BY ProvinceName ,
        BillDate";
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd);
                var group = CommHelper.ModelConvertHelper<TimeTrackingReportModel.NeceModel>.ConvertToModel(dt)
                    .GroupBy(d => d.ProvinceName);
                var list = group.SelectMany(d => d).ToList();
                foreach (var item in group)
                {
                    var today = item.FirstOrDefault(d => d.BillDate == todayTime);
                    var yestoday = item.FirstOrDefault(d => d.BillDate == yestodayTime);
                    var lastweek = item.FirstOrDefault(d => d.BillDate == lastweekTime);
                    if (today == null)
                    {
                        var newItem = new TimeTrackingReportModel.NeceModel
                        {
                            ProvinceName = item.Key,
                            BillDate = todayTime,
                            Week = $"本周{ReturnWeek(Convert.ToDateTime(todayTime))}"
                        };
                        list.Add(newItem);
                    }
                    if (yestoday == null)
                    {
                        var newItem = new TimeTrackingReportModel.NeceModel
                        {
                            ProvinceName = item.Key,
                            BillDate = yestodayTime,
                            Week = $"本周{ReturnWeek(Convert.ToDateTime(yestodayTime))}"
                        };
                        list.Add(newItem);
                    }
                    if (lastweek == null)
                    {
                        var newItem = new TimeTrackingReportModel.NeceModel
                        {
                            ProvinceName = item.Key,
                            BillDate = lastweekTime,
                            Week = $"上周{ReturnWeek(Convert.ToDateTime(lastweekTime))}"
                        };
                        list.Add(newItem);
                    }
                }
                var count = group.Count();
                var totalDateL = list.Where(d => d.BillDate == lastweekTime);
                var lFactPartAMT = totalDateL.Sum(d => d.FactPartAMT);
                var lSalePartQty = totalDateL.Sum(d => d.SalePartQty);
                var lSaleBillPartQty = totalDateL.Sum(d => d.SaleBillPartQty);
                var totalItemL = new TimeTrackingReportModel.NeceModel
                {
                    ProvinceName = "总计",
                    BillDate = lastweekTime,
                    Week = $"上周{ReturnWeek(Convert.ToDateTime(lastweekTime))}",
                    ShopQty = totalDateL.Sum(d => d.ShopQty),
                    FactAMT = totalDateL.Sum(d => d.FactAMT),
                    FactPartAMT = lFactPartAMT,
                    SalePartQty = totalDateL.Sum(d => d.SalePartQty),
                    SaleBillQty = totalDateL.Sum(d => d.SaleBillQty),
                    SaleBillPartQty = lSaleBillPartQty,
                    KpiQty = totalDateL.Sum(d => d.KpiQty),
                    UnitPrice = lSaleBillPartQty == 0 ? 0 : (lFactPartAMT / (decimal)(lSaleBillPartQty * 1.0)),
                    Closing = count == 0 ? 0 : (totalDateL.Sum(d => d.Closing) / (decimal)(count * 1.0)),
                    JointRate = lSaleBillPartQty == 0 ? 0 : (lSalePartQty / (decimal)(lSaleBillPartQty * 1.0)),
                };
                var totalDateY = list.Where(d => d.BillDate == yestodayTime);
                var yFactPartAMT = totalDateY.Sum(d => d.FactPartAMT);
                var ySalePartQty = totalDateY.Sum(d => d.SalePartQty);
                var ySaleBillPartQty = totalDateY.Sum(d => d.SaleBillPartQty);
                var totalItemY = new TimeTrackingReportModel.NeceModel
                {
                    ProvinceName = "总计",
                    BillDate = yestodayTime,
                    Week = $"上周{ReturnWeek(Convert.ToDateTime(yestodayTime))}",
                    ShopQty = totalDateY.Sum(d => d.ShopQty),
                    FactAMT = totalDateY.Sum(d => d.FactAMT),
                    FactPartAMT = yFactPartAMT,
                    SalePartQty = ySalePartQty,
                    SaleBillQty = totalDateY.Sum(d => d.SaleBillQty),
                    SaleBillPartQty = ySaleBillPartQty,
                    KpiQty = totalDateY.Sum(d => d.KpiQty),
                    UnitPrice = ySaleBillPartQty == 0 ? 0 : (yFactPartAMT / (decimal)(ySaleBillPartQty * 1.0)),
                    Closing = count == 0 ? 0 : (totalDateY.Sum(d => d.Closing) / (decimal)(count * 1.0)),
                    JointRate = ySaleBillPartQty == 0 ? 0 : (ySalePartQty / (decimal)(ySaleBillPartQty * 1.0)),
                };
                var totalDateT = list.Where(d => d.BillDate == todayTime);
                var tFactPartAMT = totalDateT.Sum(d => d.FactPartAMT);
                var tSalePartQty = totalDateT.Sum(d => d.SalePartQty);
                var tSaleBillPartQty = totalDateT.Sum(d => d.SaleBillPartQty);
                var totalItemT = new TimeTrackingReportModel.NeceModel
                {
                    ProvinceName = "总计",
                    BillDate = todayTime,
                    Week = $"上周{ReturnWeek(Convert.ToDateTime(todayTime))}",
                    ShopQty = totalDateT.Sum(d => d.ShopQty),
                    FactAMT = totalDateT.Sum(d => d.FactAMT),
                    FactPartAMT = tFactPartAMT,
                    SalePartQty = totalDateT.Sum(d => d.SalePartQty),
                    SaleBillQty = totalDateT.Sum(d => d.SaleBillQty),
                    SaleBillPartQty = tSaleBillPartQty,
                    KpiQty = totalDateT.Sum(d => d.KpiQty),
                    UnitPrice = tSaleBillPartQty == 0 ? 0 : (tFactPartAMT / (decimal)(tSaleBillPartQty * 1.0)),
                    Closing = count == 0 ? 0 : (totalDateT.Sum(d => d.Closing) / (decimal)(count * 1.0)),
                    JointRate = tSaleBillPartQty == 0 ? 0 : (tSalePartQty / (decimal)(tSaleBillPartQty * 1.0)),
                };
                list.Add(totalItemL);
                list.Add(totalItemY);
                list.Add(totalItemT);
                var newGroup = list.OrderBy(d => d.ProvinceName).ThenBy(d => d.BillDate).GroupBy(d => d.ProvinceName);
                foreach (var item in newGroup)
                {
                    var today = item.FirstOrDefault(d => d.BillDate == todayTime);
                    var yestoday = item.FirstOrDefault(d => d.BillDate == yestodayTime);
                    var lastweek = item.FirstOrDefault(d => d.BillDate == lastweekTime);
                    var neighborhoodCom_FactAMT = (yestoday?.FactAMT == 0 ? 0 : (today?.FactAMT / yestoday?.FactAMT) - 1) * 100;
                    var neighborhoodCom_SalePartQty = (yestoday?.SalePartQty == 0
                        ? 0
                        : (today?.SalePartQty / (decimal?)yestoday?.SalePartQty) - 1) * 100;
                    var neighborhoodCom_SaleBillPartQty = (yestoday?.SaleBillPartQty == 0
                        ? 0
                        : (today?.SaleBillPartQty / (decimal?)yestoday?.SaleBillPartQty) - 1) * 100;
                    var neighborhoodCom_KpiQty = (yestoday?.KpiQty == 0
                        ? 0
                        : (today?.KpiQty / (decimal?)yestoday?.KpiQty) - 1) * 100;
                    var neighborhoodCom_UnitPrice = (yestoday?.UnitPrice == 0 ? 0 : (today?.UnitPrice / yestoday?.UnitPrice) - 1) * 100;
                    var neighborhoodCom_Closing = (yestoday?.Closing == 0 ? 0 : (today?.Closing / yestoday?.Closing) - 1) * 100;
                    var neighborhoodCom_JointRate = (yestoday?.JointRate == 0 ? 0 : (today?.JointRate / yestoday?.JointRate) - 1) * 100;
                    var weekCom_FactAMT = (lastweek?.FactAMT == 0 ? 0 : (today?.FactAMT / lastweek?.FactAMT) - 1) * 100;
                    var weekCom_SalePartQty = (lastweek?.SalePartQty == 0
                        ? 0
                        : (today?.SalePartQty / (decimal?)lastweek?.SalePartQty) - 1) * 100;
                    var weekCom_SaleBillPartQty = (lastweek?.SaleBillPartQty == 0
                        ? 0
                        : (today?.SaleBillPartQty / (decimal?)lastweek?.SaleBillPartQty) - 1) * 100;
                    var weekCom_KpiQty = (lastweek?.KpiQty == 0 ? 0 : (today?.KpiQty / (decimal?)lastweek?.KpiQty) - 1) * 100;
                    var weekCom_UnitPrice = (lastweek?.UnitPrice == 0 ? 0 : (today?.UnitPrice / lastweek?.UnitPrice) - 1) * 100;
                    var weekCom_Closing = (lastweek?.Closing == 0 ? 0 : (today?.Closing / lastweek?.Closing) - 1) * 100;
                    var weekCom_JointRate = (lastweek?.JointRate == 0 ? 0 : (today?.JointRate / lastweek?.JointRate) - 1) * 100;
                    foreach (var it in item)
                    {
                        var datetime = Convert.ToDateTime(it.BillDate);
                        var str = ReturnWeek(datetime);
                        if (datetime.Year == DateTime.Now.Year && datetime.Month == DateTime.Now.Month &&
                            (datetime.Day == DateTime.Now.Day || datetime.Day == DateTime.Now.AddDays(-1).Day))
                        {
                            it.Week = $"本周{str}";
                        }
                        else
                        {
                            it.Week = $"上周{str}";
                        }
                        it.NeighborhoodCom_FactAMT = neighborhoodCom_FactAMT ?? 0;
                        it.NeighborhoodCom_SalePartQty = neighborhoodCom_SalePartQty ?? 0;
                        it.NeighborhoodCom_SaleBillPartQty = neighborhoodCom_SaleBillPartQty ?? 0;
                        it.NeighborhoodCom_KpiQty = neighborhoodCom_KpiQty ?? 0;
                        it.NeighborhoodCom_UnitPrice = neighborhoodCom_UnitPrice ?? 0;
                        it.NeighborhoodCom_Closing = neighborhoodCom_Closing ?? 0;
                        it.NeighborhoodCom_JointRate = neighborhoodCom_JointRate ?? 0;
                        it.WeekCom_FactAMT = weekCom_FactAMT ?? 0;
                        it.WeekCom_SalePartQty = weekCom_SalePartQty ?? 0;
                        it.WeekCom_SaleBillPartQty = weekCom_SaleBillPartQty ?? 0;
                        it.WeekCom_KpiQty = weekCom_KpiQty ?? 0;
                        it.WeekCom_UnitPrice = weekCom_UnitPrice ?? 0;
                        it.WeekCom_Closing = weekCom_Closing ?? 0;
                        it.WeekCom_JointRate = weekCom_JointRate ?? 0;
                    }
                }
                var finalGroup = newGroup.Select(item => new TimeTrackingReportModel.ListModel
                {
                    ProvinceName = item.Key,
                    group = item.GroupBy(d => d.ProvinceName + "_" + d.BillDate).ToList()
                }).ToList();
                return finalGroup;
            }
            catch (Exception ex)
            {
                Log.Error(ex);
                return null;
            }
        }

        public static string ReturnWeek(DateTime dt)
        {
            var str = "";
            switch (dt.DayOfWeek)
            {
                case DayOfWeek.Sunday:
                    str = "日";
                    break;
                case DayOfWeek.Monday:
                    str = "一";
                    break;
                case DayOfWeek.Tuesday:
                    str = "二";
                    break;
                case DayOfWeek.Wednesday:
                    str = "三";
                    break;
                case DayOfWeek.Thursday:
                    str = "四";
                    break;
                case DayOfWeek.Friday:
                    str = "五";
                    break;
                case DayOfWeek.Saturday:
                    str = "六";
                    break;
                default:
                    break;
            }
            return str;
        }
    }
}
